<?php

require_once './_AddinLibraries/database_driver.php';

//function classID_list_query() {
//    select_itec_database();
//    $classes = array();
//    $sql = db_query("SELECT ClassID FROM class");
//    while ($erg = $sql->fetchAssoc()) {
//        if ($erg == NULL)
//            break;
//        $classes[] = $erg['classid'];
//    };
//    select_drupal_database();
//    return $classes;
//}

function student_query($classID) {
    $student = array();
    $headers = header_query('student');
    select_itec_database();
    $sql = db_query("call sp_GetListOfStudentByClass('".$classID."')");
    $i=0;
    while ($erg = $sql->fetchAssoc()) {
        if ($erg == NULL)
            break;
        foreach($headers as $header){
            $header = strtolower($header);
            $student[$i][$header] = $erg[$header];
        }
        ++$i;
    };
    //drupal_set_message(t('The queries CLASS MEMBER have been made.'));
    select_drupal_database();
    return $student;
}

function header_query($tablename) {
    select_itec_database();
    $header = array();
    $sql = db_query("call sp_GetColumnNames('".$tablename."')");
    while ($erg = $sql->fetchAssoc()) {
        if ($erg == NULL)
            break;
        $header[] = $erg['class'];
    };
    select_drupal_database();
    //$header = drupal_map_assoc($header);
    return $header;
}

function add_course($id, $name, $program, $type, $credits, $status, $note) {
    select_itec_database();
    $sql = db_query("call sp_AddCourse('".$id."','".$name."','".$program."','".$type."','".$credits."','".$status."','".$note."')");
    $result = $sql->fetchAssoc();
    select_drupal_database();
    
    return $result['result'];
}

function get_course_status() {

    select_itec_database();
    $header = array();
    $sql = db_query("call sp_GetCourseStatus()");
    while ($erg = $sql->fetchAssoc()) {
        if ($erg == NULL)
            break;
        $header[] = $erg['description'];
    };

    select_drupal_database();
    
    return $header;
}

function get_course_type() {

    select_itec_database();
    $header = array();
    $sql = db_query("call sp_GetCourseType()");
    while ($erg = $sql->fetchAssoc()) {
        if ($erg == NULL)
            break;
        $header[] = $erg['description'];
    };

    select_drupal_database();
    
    return $header;
}

function get_course_type_id($type) {

    select_itec_database();
    $sql = db_query("call sp_GetCourseTypeID()");
    for($i = 0; $i <= $type; $i++)
        $result = $sql->fetchAssoc ();

    select_drupal_database();
    
    return $result['coursetypeid'];
}

function get_course_info($id) {

    select_itec_database();
    $sql = db_query("call sp_GetCourseInfo('".$id."')");
    $result = $sql->fetchAssoc ();

    select_drupal_database();
    
    return $result;
}

function get_course_status_from_id($id) {
//    select_itec_database();
//    $sql = db_query("call sp_GetCourseStatusFromID(".$id.")");
//    $result = $sql->fetchAssoc();
//    select_drupal_database();
    
    return $id;
}

function get_course_type_from_id($id) {
    select_itec_database();
    $sql = db_query("call sp_GetCourseTypeFromID('".$id."')");
    $result = $sql->fetchAssoc();
    select_drupal_database();
    
    return $result;
}

function update_course($id, $name, $program, $type, $nCredit, $status, $n)
{
    select_itec_database();
    $sql = db_query("call sp_UpdateCourse('".$id."','".$name."','".$program."','".$type."','".$nCredit."','".$status."','".$n."')");
    $result = $sql->fetchAssoc();
    select_drupal_database();
    
    return $result['result'];
}

function get_prerequisite($id)
{
    select_itec_database();
    $sql = db_query("call sp_GetPrerequisite('".$id."')");
    $result = array();
    
    while ($erg = $sql->fetchAssoc()) {
        if ($erg == NULL)
            break;
        $result[] = $erg['courseid'];
    };
    select_drupal_database();
    
    return $result;
}

function get_course_list()
{
    $result = array();
    select_itec_database();
    $sql = db_query('call sp_GetCourseList(null, null)');
    while($erg = $sql->fetchAssoc()) {
        if($erg == null)
            break;
        $result[] = $erg['courseid'];
    };
    
    select_drupal_database();
    
    return $result;
}

function get_program_index($programid) {
    select_itec_database();
    $sql = db_query("select programid from program");
    $count = 1;
    $fetchValue = $sql->fetchAssoc();
    while($programid != $fetchValue['programid']) {
        $count ++;
        $fetchValue = $sql->fetchAssoc();
    }
    select_drupal_database();
    
    return $count;
}